prompt
prompt 检查表prd_agency 表是否存在，不存在则创建......
declare v_rowcount number(10);
begin
  select count(1) into v_rowcount from user_tables where table_name = upper('prd_agency');
  if v_rowcount = 0 then
    execute immediate '
    CREATE TABLE HS_PRD.prd_agency
    (
        update_time                    number(10,0)      DEFAULT to_number(to_char(sysdate,''hh24miss'')) NOT NULL,
        agency_no                      varchar2(10)      DEFAULT '' ''      NOT NULL,
        prodta_no                      varchar2(24)      DEFAULT '' ''      NOT NULL,
        update_date                    number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        agency_name                    varchar2(180)     DEFAULT '' ''      NOT NULL,
        company_name                   varchar2(180)     DEFAULT '' ''      NOT NULL,
        company_no                     number(10,0)      DEFAULT 0          NOT NULL
    )    STORAGE(FREELISTS 20 FREELIST GROUPS 2)
    NOLOGGING TABLESPACE HS_PRD_DATA ';

    execute immediate 'CREATE UNIQUE INDEX HS_PRD.uk_agency ON HS_PRD.prd_agency(prodta_no ASC,company_no ASC)';

    execute immediate 'comment on table HS_PRD.prd_agency is ''代销商信息表''';
    execute immediate 'comment on column HS_PRD.prd_agency.update_time is ''记录更新时间''';
    execute immediate 'comment on column HS_PRD.prd_agency.agency_no is ''销售商代码''';
    execute immediate 'comment on column HS_PRD.prd_agency.prodta_no is ''产品TA编号''';
    execute immediate 'comment on column HS_PRD.prd_agency.update_date is ''记录更新日期''';
    execute immediate 'comment on column HS_PRD.prd_agency.agency_name is ''销售商名称''';
    execute immediate 'comment on column HS_PRD.prd_agency.company_name is ''公司名称''';
    execute immediate 'comment on column HS_PRD.prd_agency.company_no is ''券商编号''';

    execute immediate 'DELETE HS_PRD.hsobjects WHERE object_name = ''prd_agency''';
    execute immediate 'INSERT INTO HS_PRD.hsobjects (object_name, object_id, own_base, object_type, object_version,service_name) values (''prd_agency'', 0, ''HS_PRD_DATA'', ''U'', ''V1.0.0'', ''prd'')';

    commit;
  end if;
end;
/

-- 表prd_busin_entrust全量脚本
prompt
prompt 检查表prd_busin_entrust 表是否存在，不存在则创建......
declare v_rowcount number(10);
begin
  select count(1) into v_rowcount from user_tables where table_name = upper('prd_busin_entrust');
  if v_rowcount = 0 then
    execute immediate '
    CREATE TABLE HS_PRD.prd_busin_entrust
    (
        init_date                      number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        entrust_no                     number(20,0)      DEFAULT 0          NOT NULL,
        curr_date                      number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        curr_milltime                  number(10,0)      DEFAULT to_number(to_char(systimestamp, ''HH24MISSff3'')) NOT NULL,
        op_station                     varchar2(255)     DEFAULT '' ''      NOT NULL,
        op_branch_no                   number(10,0)      DEFAULT 0          NOT NULL,
        operator_no                    varchar2(18)      DEFAULT '' ''      NOT NULL,
        op_entrust_way                 char(1)           DEFAULT '' ''      NOT NULL,
        prod_entrustsrc_type           char(1)           DEFAULT '' ''      NOT NULL,
        allot_no                       varchar2(24)      DEFAULT '' ''      NOT NULL,
        batch_no                       number(20,0)      DEFAULT 0          NOT NULL,
        original_appno                 varchar2(24)      DEFAULT '' ''      NOT NULL,
        fund_account                   varchar2(18)      DEFAULT '' ''      NOT NULL,
        client_id                      varchar2(18)      DEFAULT '' ''      NOT NULL,
        prod_account                   varchar2(18)      DEFAULT '' ''      NOT NULL,
        trans_account                  varchar2(32)      DEFAULT '' ''      NOT NULL,
        pay_account                    varchar2(32)      DEFAULT '' ''      NOT NULL,
        pay_kind                       char(1)           DEFAULT '' ''      NOT NULL,
        bank_no                        varchar2(6)       DEFAULT '' ''      NOT NULL,
        oppo_prodaccount               varchar2(18)      DEFAULT '' ''      NOT NULL,
        other_transaccount             varchar2(32)      DEFAULT '' ''      NOT NULL,
        developer                      varchar2(60)      DEFAULT '' ''      NOT NULL,
        ta_type                        char(1)           DEFAULT '' ''      NOT NULL,
        prodta_no                      varchar2(24)      DEFAULT '' ''      NOT NULL,
        prod_code                      varchar2(32)      DEFAULT '' ''      NOT NULL,
        prod_name                      varchar2(180)     DEFAULT '' ''      NOT NULL,
        trade_report_type              char(1)           DEFAULT '' ''      NOT NULL,
        money_type                     varchar2(3)       DEFAULT '' ''      NOT NULL,
        dividend_way                   char(1)           DEFAULT '' ''      NOT NULL,
        charge_type                    char(1)           DEFAULT '' ''      NOT NULL,
        prodcode_type                  char(1)           DEFAULT '' ''      NOT NULL,
        prod_type_ass                  varchar2(4)       DEFAULT '' ''      NOT NULL,
        discount_rate                  number(9,8)       DEFAULT 0.0        NOT NULL,
        switch_prod_code               varchar2(32)      DEFAULT '' ''      NOT NULL,
        switch_charge_type             char(1)           DEFAULT '' ''      NOT NULL,
        entrust_balance                number(15,2)      DEFAULT 0.0        NOT NULL,
        entrust_amount                 number(15,2)      DEFAULT 0.0        NOT NULL,
        entrust_price                  number(23,8)      DEFAULT 0.0        NOT NULL,
        otc_entrust_status             char(1)           DEFAULT '' ''      NOT NULL,
        invpay_status                  char(1)           DEFAULT '' ''      NOT NULL,
        prod_revisit_status            char(1)           DEFAULT '' ''      NOT NULL,
        verify_status                  char(1)           DEFAULT '' ''      NOT NULL,
        settle_date                    number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        business_balance               number(15,2)      DEFAULT 0.0        NOT NULL,
        business_amount                number(15,2)      DEFAULT 0.0        NOT NULL,
        business_date                  number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        ta_serialno                    varchar2(24)      DEFAULT '' ''      NOT NULL,
        error_info                     varchar2(500)     DEFAULT '' ''      NOT NULL,
        error_code                     varchar2(8)       DEFAULT '' ''      NOT NULL,
        prodrisk_level                 number(10,0)      DEFAULT 0          NOT NULL,
        corp_risk_level                number(10,0)      DEFAULT 0          NOT NULL,
        ta_risk_level                  number(10,0)      DEFAULT 0          NOT NULL,
        risk_enddate                   number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        prodrisk_flag                  char(1)           DEFAULT '' ''      NOT NULL,
        elig_check_str                 varchar2(255)     DEFAULT '' ''      NOT NULL,
        cancel_serial_no               varchar2(128)     DEFAULT '' ''      NOT NULL,
        elig_confirm_type              char(1)           DEFAULT '' ''      NOT NULL,
        agreement_id_str               varchar2(2000)    DEFAULT '' ''      NOT NULL,
        prod_prop                      varchar2(3)       DEFAULT '' ''      NOT NULL,
        redeem_type                    char(1)           DEFAULT '' ''      NOT NULL,
        register_date                  number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        agency_no                      varchar2(10)      DEFAULT '' ''      NOT NULL,
        other_agency                   varchar2(10)      DEFAULT '' ''      NOT NULL,
        ratio_sx                       number(9,8)       DEFAULT 0.0        NOT NULL,
        sale_fare                      number(15,2)      DEFAULT 0.0        NOT NULL,
        other_fare                     number(15,2)      DEFAULT 0.0        NOT NULL,
        exceed_flag                    char(1)           DEFAULT '' ''      NOT NULL,
        notrade_kind                   char(1)           DEFAULT '' ''      NOT NULL,
        contract_id                    varchar2(36)      DEFAULT '' ''      NOT NULL,
        deduct_date                    number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        period_date                    number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        pay_cycle_unit                 char(1)           DEFAULT '' ''      NOT NULL,
        pay_cycle_rate                 number(10,0)      DEFAULT 0          NOT NULL,
        id_kind                        char(1)           DEFAULT '' ''      NOT NULL,
        id_no                          varchar2(40)      DEFAULT '' ''      NOT NULL,
        organ_flag                     char(1)           DEFAULT '' ''      NOT NULL,
        net_no                         varchar2(64)      DEFAULT '' ''      NOT NULL,
        other_netno                    varchar2(10)      DEFAULT '' ''      NOT NULL,
        otc_deal_flag                  char(1)           DEFAULT '' ''      NOT NULL,
        valid_date                     number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        date_clear                     number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        remark                         varchar2(2000)    DEFAULT '' ''      NOT NULL,
        position_str                   varchar2(100)     DEFAULT '' ''      NOT NULL,
        report_no                      number(20,0)      DEFAULT 0          NOT NULL,
        report_date                    number(10,0)      DEFAULT to_number(to_char(sysdate,''yyyymmdd'')) NOT NULL,
        prodcode_sub_type              varchar2(4)       DEFAULT '' ''      NOT NULL,
        elig_remark                    varchar2(2000)    DEFAULT '' ''      NOT NULL,
        report_time                    number(10,0)      DEFAULT to_number(to_char(sysdate,''hh24miss'')) NOT NULL,
        after_discount_rate            number(9,8)       DEFAULT 0.0        NOT NULL,
        protocol_no                    varchar2(20)      DEFAULT '' ''      NOT NULL,
        ip_address                     varchar2(32)      DEFAULT '' ''      NOT NULL,
        mac_address                    varchar2(32)      DEFAULT '' ''      NOT NULL,
        imei                           varchar2(128)     DEFAULT '' ''      NOT NULL,
        uuid                           varchar2(128)     DEFAULT '' ''      NOT NULL,
        cooling_end_datemilltime       varchar2(18)      DEFAULT '' ''      NOT NULL,
        prodchannel_type               char(1)           DEFAULT '' ''      NOT NULL,
        prod_cash_mode                 char(1)           DEFAULT '' ''      NOT NULL,
        firm_id                        varchar2(8)       DEFAULT '' ''      NOT NULL,
        pre_order_id                   varchar2(64)      DEFAULT '' ''      NOT NULL,
        original_ta_serialno           varchar2(24)      DEFAULT '' ''      NOT NULL
    )    STORAGE(FREELISTS 20 FREELIST GROUPS 2)
    NOLOGGING TABLESPACE HS_PRD_DATA ';

    execute immediate 'CREATE UNIQUE INDEX HS_PRD.uk_businentrust ON HS_PRD.prd_busin_entrust(allot_no ASC)';
    execute immediate 'CREATE INDEX HS_PRD.idx_businentrust_id ON HS_PRD.prd_busin_entrust(client_id ASC)';
    execute immediate 'CREATE INDEX HS_PRD.idx_businentrust_ent ON HS_PRD.prd_busin_entrust(entrust_no ASC,init_date ASC)';
    execute immediate 'CREATE INDEX HS_PRD.idx_businentrust_prodacct ON HS_PRD.prd_busin_entrust(prod_account ASC)';

    execute immediate 'comment on table HS_PRD.prd_busin_entrust is ''产品业务委托表''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.init_date is ''交易日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.entrust_no is ''委托编号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.curr_date is ''当前日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.curr_milltime is ''发生时间（毫秒）''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.op_station is ''站点地址''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.op_branch_no is ''操作分支机构''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.operator_no is ''操作员编号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.op_entrust_way is ''委托方式''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prod_entrustsrc_type is ''委托来源类别''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.allot_no is ''申请编号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.batch_no is ''委托批号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.original_appno is ''原申请单编号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.fund_account is ''资产账户''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.client_id is ''客户编号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prod_account is ''产品账号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.trans_account is ''交易账号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.pay_account is ''支付账户''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.pay_kind is ''支付方式''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.bank_no is ''银行代码''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.oppo_prodaccount is ''对方产品账号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.other_transaccount is ''对方交易账号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.developer is ''开发人员''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.ta_type is ''TA类型''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prodta_no is ''产品TA编号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prod_code is ''产品代码''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prod_name is ''产品名称''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.trade_report_type is ''交易委托申报类型''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.money_type is ''币种类别''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.dividend_way is ''分红方式''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.charge_type is ''前后收费类型''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prodcode_type is ''产品代码类别''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prod_type_ass is ''产品辅助类别''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.discount_rate is ''折扣比率''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.switch_prod_code is ''转换代码''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.switch_charge_type is ''转换收费类型''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.entrust_balance is ''委托金额''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.entrust_amount is ''委托数量''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.entrust_price is ''委托价格''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.otc_entrust_status is ''委托状态''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.invpay_status is ''资金支付状态''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prod_revisit_status is ''回访状态''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.verify_status is ''审核状态''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.settle_date is ''交收日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.business_balance is ''成交金额''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.business_amount is ''成交数量''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.business_date is ''成交日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.ta_serialno is ''TA确认流水号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.error_info is ''错误提示''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.error_code is ''错误代码''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prodrisk_level is ''风险等级''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.corp_risk_level is ''客户风险等级''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.ta_risk_level is ''TA端客户风险等级''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.risk_enddate is ''风险截止日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prodrisk_flag is ''异常类型''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.elig_check_str is ''适当性信息校验串''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.cancel_serial_no is ''冲销流水号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.elig_confirm_type is ''适当性信息确认类型''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.agreement_id_str is ''协议编号串''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prod_prop is ''交易属性''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.redeem_type is ''赎回类型''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.register_date is ''登记日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.agency_no is ''销售商代码''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.other_agency is ''对方销售商''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.ratio_sx is ''手续费率''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.sale_fare is ''销售费用''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.other_fare is ''其他费用''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.exceed_flag is ''巨额赎回处理办法''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.notrade_kind is ''非交易过户种类''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.contract_id is ''合同号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.deduct_date is ''扣款日''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.period_date is ''定期定额日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.pay_cycle_unit is ''扣款周期单位''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.pay_cycle_rate is ''扣款周期频率''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.id_kind is ''证件类别''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.id_no is ''证件号码''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.organ_flag is ''客户类型''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.net_no is ''销售网点编号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.other_netno is ''对方销售网点编号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.otc_deal_flag is ''场外处理标志''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.valid_date is ''有效日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.date_clear is ''清算日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.remark is ''备注''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.position_str is ''定位串''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.report_no is ''申请编号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.report_date is ''申报日期''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prodcode_sub_type is ''产品代码子类别''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.elig_remark is ''适当性备注''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.report_time is ''申报时间''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.after_discount_rate is ''后收费折扣率''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.protocol_no is ''协议号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.ip_address is ''登录ip地址''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.mac_address is ''注册MAC地址''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.imei is ''国际移动设备识别码''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.uuid is ''通用唯一识别码''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.cooling_end_datemilltime is ''委托冷静期结束日期时间''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prodchannel_type is ''产品渠道类型''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.prod_cash_mode is ''现金理财模式''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.firm_id is ''结算会员号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.pre_order_id is ''预约订单号''';
    execute immediate 'comment on column HS_PRD.prd_busin_entrust.original_ta_serialno is ''TA原确认流水号''';

    execute immediate 'DELETE HS_PRD.hsobjects WHERE object_name = ''prd_busin_entrust''';
    execute immediate 'INSERT INTO HS_PRD.hsobjects (object_name, object_id, own_base, object_type, object_version,service_name) values (''prd_busin_entrust'', 0, ''HS_PRD_DATA'', ''U'', ''V1.0.11'', ''prd'')';

    commit;
  end if;
end;
/